import pymysql
import pandas as pd
import numpy as np


###############参数设置区域######################
###取表数据库参数
host1 = "bj-cdb-0ds18k8v.sql.tencentcdb.com"
user1 = "root"
password1 = "Huayuan@2018"
db1 = "zhongtou"
port1 = 63167
table1 = 'final_tag_4'  #lc
###写表数据库参数
host2 = "bj-cdb-0ds18k8v.sql.tencentcdb.com"
user2 = "root"
password2 = "Huayuan@2018"
db2 = "zhongtou"
port2 = 63167
table2 = 'result'
#################################################

tag = ['login_freq',
       'buy_mean',
       'login_night',
       'risk_index',
       'bank_trust_index',
       'rtqw_index',
       'payment_index',
       'social_act_index',
       'swjj_index'
       ]
       
ind = ['login_freq',
       'buy_mean',
       'login_night',
       'risk_index',
       'bank_trust_index',
       'rtqw_index',
       'payment_index',
       'social_act_index',
       'swjj_index',
       'open_count'
       ]
       
tag_out = ['risk_index',
           'bank_trust_index',
           'rtqw_index',
           'payment_index',
           'social_act_index',
           'swjj_index',
           'close_relation_index',
           'incentive_index'
           ]
           
fin = ['lsfx_index',
'fund_index',
 'nhg_index',
 'swjj_index',
 'close_relation_index',
 'hvalue_customer_index',
 'bank_trust_index',
 'qbfe_index',
 'incentive_index',
 'rtqw_index',
 'social_act_index',
 'user_id',
 'gslc_index',
 'payment_index']
 
input_field = ['user_id',
 'gender',
 'age',
 'login_freq',
 'buy_mean',
 'open_count',
 'login_night',
 'risk_index',
 'bank_trust_index',
 'rtqw_index',
 'payment_index',
 'social_act_index',
 'swjj_index',
 'hvalue_customer_index',
 'gslc_index',
 'nhg_index',
 'fund_index',
 'lsfx_index',
 'qbfe_index']
 
line = '('+','.join(fin)+')'
       
rule = pd.read_pickle('param.pkl')
rul = rule.loc[ind].values
rul_out = rule.loc[tag_out].values

def get_data():
    '''取数据'''
    l = input_field
    li = ','.join(l)
    conn = pymysql.connect(host=host1,user=user1,password=password1,db=db1,port=port1)
    cursor = conn.cursor()

    command = "select "+li+" from "+table1
    cursor.execute(command)
    a = cursor.fetchall() 
    z = pd.DataFrame(list(a),columns=l)
    return z
    
def establish_table():
    '''建表'''
    conn = pymysql.connect(host=host2,user=user2,password=password2,db=db2,port=port2)
    cursor = conn.cursor()
    command = "drop table if exists "+table2
    cursor.execute(command)
    command = "CREATE TABLE "+table2+"(\
		user_id VARCHAR(256),\
		bank_trust_index FLOAT,\
		rtqw_index FLOAT,\
		payment_index FLOAT,\
		social_act_index FLOAT,\
		swjj_index FLOAT,\
          close_relation_index FLOAT,\
          incentive_index FLOAT,\
		hvalue_customer_index INT,\
           nhg_index INT,\
		gslc_index INT,\
          fund_index INT,\
		lsfx_index INT,\
		qbfe_index FLOAT\
	)"

    cursor.execute(command)
    
def score(x):
    '''转换成标准分'''
    y = 0.5 + 0.25*(3*x-x**3)
    return y

def guiyi(x):
    '''归一化'''
    t = x.dot(x.T)[0,0]
    if t == 0:
        return x
    else:
        return x/np.sqrt(t)

def fenwei(ser,n=10):
    '''统计整个分位数'''
    v = list(ser.values)
    v.sort()
    N = len(v)
    l = []
    for i in range(1,n):
        l.append(v[int(i/n*N)])
    return l
    
def cal(x,dist):
    '''计算单个体分位数'''
    n = len(dist) + 1
    for i in range(n-1):
        if x <= dist[i]:
            return (i+1)*1.0/n
    return 1.0
    
def ser_to_ocean(ser):
    '''数据到OCEAN'''
    pool = {}
    for it in tag:
        w = cal(ser[it],dic[it])   
        pool[it] = w
    it = 'open_count'
    w = ser[it]
    pool[it] = w
    out = pd.DataFrame([pool])[ind]
    v = out.values - 0.5
    p = guiyi(v.dot(rul))
    return p
    
def ser_complete(ser):
    p = ser_to_ocean(ser)
    sim = rul_out.dot(p.T)
    scor = score(sim) * 100
    se_new = {}
    for i in range(len(tag_out)):
        se_new[tag_out[i]] = scor[i,0]

    ret = {}
    ret['user_id'] = ser['user_id']

    #####以下是行为型#####
    remain = ['hvalue_customer_index',
              'nhg_index',
              'gslc_index',
              'lsfx_index',
              'qbfe_index'
              ]
    for it in remain:
        ret[it] = ser[it]

    if ser['user_id'] == '1':
        print(ser)
    if ser['fund_index'] == 0:
        ret['fund_index'] = 0
    else:

        risk = ser['risk_index']*0.7 + se_new['risk_index']*0.3
        print(ser['user_id'],ser['risk_index'],se_new['risk_index'])
        ret['fund_index'] = 5 - int(risk/20)

    #####以下是特征型#####
    only = ['close_relation_index','incentive_index']
    for it in only:
        ret[it] = se_new[it]

    #####以下是混合型#####
    it = 'bank_trust_index'
    ret[it] = 0.5*ser[it] + 0.5*se_new[it]

    it = 'rtqw_index'
    ret[it] = 0.3*ser[it] + 0.7*se_new[it]

    it = 'payment_index'
    ret[it] = 0.5*ser[it] + 0.5*se_new[it]

    it = 'social_act_index'
    ret[it] = 0.3*ser[it] + 0.7*se_new[it]
    
    it = 'swjj_index'
    ret[it] = min(0.7*ser[it] + 0.3*se_new[it],100)  
    
    #####规整输出#####
    need_int = ['hvalue_customer_index',
                'gslc_index',
                'nhg_index',
                'fund_index',
                'lsfx_index'
                ]
    for k in ret.keys():
        if k == 'user_id':
            continue
        elif k in need_int:
            ret[k] = int(ret[k])
        else:
            ret[k] = round(ret[k],2)
    
    return ret
    



if __name__ == '__main__':    
    ################建表与取数##################
    print('建表中...')
    establish_table()
    print('建表完毕!')
    print('取数中...')
    z = get_data()
    print('取数完毕!')
    ############计算分位数##################
    print('分位数计算中...')
    dic = {}
    for it in tag:
        dic[it] = fenwei(z[it])
    print('分位数计算完毕!')
    ############补标签并写库######################
    conn = pymysql.connect(host=host2,user=user2,password=password2,db=db2,port=port2)
    cursor = conn.cursor()
    
    for i in z.index:
        ser = z.loc[i]
        ret = ser_complete(ser)
        
        st = '('
        for j in fin:
            a = ret[j]
            if type(a) == str:
                st = st + '"' + a + '"' + ','
            else:
                st = st + str(a) + ','
        st = st[:-1]
        st = st + ')'
        command = "insert into "+table2+line+" values"+st
        cursor.execute(command)
        conn.commit()
        
        
    
    
        














